package br.com.quiz.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import br.com.quiz.domain.Disciplina;
import br.com.quiz.domain.DisciplinaProfessor;
import br.com.quiz.domain.Professor;
import br.com.quiz.factory.ConexaoFactory;
import br.com.quiz.util.HibernateUtil;

public class DisciplinaProfessorDAO{
	public static List< DisciplinaProfessor > listar() {
		Session sessao = HibernateUtil.getSessionFactory().openSession();
		List< DisciplinaProfessor > listaDisciplinaProfessor = null;
		try {
			Query consulta = sessao.getNamedQuery( "DisciplinaProfessor.listar" );
			listaDisciplinaProfessor = consulta.list();
		} catch ( RuntimeException e ) {
			throw e;
		} finally {
			sessao.close();
		}

		return listaDisciplinaProfessor;
	}

	public static List< DisciplinaProfessor > filtrarIdProfessor( Professor p ) {
		Session sessao = HibernateUtil.getSessionFactory().openSession();
		List disciplinas;
		try {
			StringBuffer hql = new StringBuffer();
			hql.append( "SELECT d FROM DisciplinaProfessor d " );
			// hql.append("where d.professor := " );
			// hql.append("where d.professor = " + p.getProId());

			Query query = sessao.createQuery( hql.toString() );
			disciplinas = query.list();

			return disciplinas;

		} catch ( RuntimeException ex ) {
			sessao.cancelQuery();
		} finally {
			sessao.close();
		}
		return null;
	}

	public static ArrayList< Disciplina > listarDisciplinasPorProfessor( Professor p ) {
		Connection con;
		PreparedStatement ps;

		try {
			con = ConexaoFactory.conectar();
			ArrayList< Disciplina > disciplinas = new ArrayList();
			String sql = "select d.DIS_ID, d.DIS_DESCRICAO " + "from disciplina d inner join disciplina_professor disProf " + "on disProf.DIP_DIS_ID=d.DIS_ID " + "inner join professor p on p.PRO_ID=disProf.DIP_PRO_ID " + "where p.PRO_ID = ? ";
			ps = con.prepareStatement( sql );
			ps.setInt( 1, p.getProId() );
			System.out.println( ps );
			ResultSet rs = ps.executeQuery();

			while ( rs.next() ) {
				Disciplina d = new Disciplina();
				d.setDisId( rs.getInt( "DIS_ID" ) );
				d.setDisDescricao( rs.getString( "DIS_DESCRICAO" ) );
				disciplinas.add( d );
				System.out.println( d );
			}
			System.out.println( "Listagem de disciplinas obtidas com sucesso!" );
			return disciplinas;
		} catch ( Exception e ) {
			System.out.println( "Falha ao listar disciplinas " + e.getMessage() );
			e.printStackTrace();
		}
		return null;
	}

	public static void incluirDisciplinaProfessor( Professor p, Disciplina d ) throws SQLException {
		Connection con = null;
		PreparedStatement ps;
		try {
			con = ConexaoFactory.conectar();
			String sql = "insert into disciplina_professor (DIP_DIS_ID, DIP_PRO_ID)" + " values(?,?)";

			ps = con.prepareStatement( sql );
			ps.setInt( 1, d.getDisId() );
			ps.setInt( 2, p.getProId() );
			System.out.println( ps );
			ps.executeUpdate();

			System.out.println( "inserido com sucesso!" );
		} catch ( Exception e ) {
			con.rollback();
			System.out.println( "Erro: " + e.getMessage() );
		}

	}

	public static void excluir( Professor p, Disciplina d ) throws SQLException {
		Connection con = null;
		PreparedStatement ps;

		try {
			con = ConexaoFactory.conectar();
			String sql = "delete from disciplina_professor where DIP_PRO_ID=? and DIP_DIS_ID=?;";
			ps = con.prepareStatement( sql );
			ps.setInt( 1, p.getProId() );
			ps.setInt( 2, d.getDisId() );
			ps.executeUpdate();

		} catch ( Exception e ) {
			con.rollback();
			System.out.println( "Erro: " + e.getMessage() );
		}

	}

	public static boolean temOuNao( Professor p, Disciplina d ) {
		Connection con;
		PreparedStatement ps;
		Integer total = null;
		try {
			con = ConexaoFactory.conectar();

			String sql = "select count(dp.DIP_ID) from disciplina_professor dp where dp.DIP_PRO_ID=? and dp.DIP_DIS_ID=? ";
			ps = con.prepareStatement( sql );
			ps.setInt( 1, p.getProId() );
			ps.setInt( 2, d.getDisId() );
			System.out.println( ps );
			ResultSet rs = ps.executeQuery();

			while ( rs.next() ) {
				total = rs.getInt( "count(dp.DIP_ID)" );
			}
			if(total>0){
				return true;
			}

		} catch ( Exception e ) {
			System.out.println( "Falha ao listar disciplinas " + e.getMessage() );
			e.printStackTrace();
		}
	
		return false;
	}

}
